package cz.acies.uniql.dialect;

import java.sql.*;
import java.util.*;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;


import cz.acies.uniql.connect.*;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.IndexesMetaData;
import cz.acies.uniql.metadata.PrimaryKeyMetaData;
import cz.acies.uniql.metadata.TableMetaData;
import cz.acies.uniql.sql.AlterTable;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLColumn;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLConditionGroup;
import cz.acies.uniql.sql.element.UniQLCriterionElement;
import cz.acies.uniql.sql.element.UniQLField;
import cz.acies.uniql.sql.element.UniQLFrom;
import cz.acies.uniql.sql.element.UniQLGroupBy;
import cz.acies.uniql.sql.element.UniQLJoin;
import cz.acies.uniql.sql.element.UniQLLimit;
import cz.acies.uniql.sql.element.UniQLOrderBy;
import cz.acies.uniql.sql.element.UniQLOrderByElement;
import cz.acies.uniql.sql.element.UniQLSubQuery;
import cz.acies.uniql.sql.element.UniQLValue;
import cz.acies.uniql.sql.element.UniQLWhere;
import cz.acies.uniql.sql.functions.SQLAgregFunc;
import cz.acies.uniql.sql.functions.SQLFunc;
import cz.acies.uniql.sql.functions.SQLSpecFunc;
import cz.acies.utils.StringUtils;

public class MSSQLDialect extends Dialect
{
	// DBMetaData variables
	// TableMetaData variables
	// FieldMetadata variables	
	// PrimaryKeyMetaData variables
	// IndexMetaData variables
	
	// Convert Element variables
	// Convert values variables
	
	// * DDL methods *
	// * FullText methods *
	// * LOCK & UNLOCK TABLE methtods *

	public MSSQLDialect() {
		super();
		
		registerColumnType(Types.BIT, "tinyint" );
		registerColumnType(Types.BIGINT, "numeric(19,0)" );
		registerColumnType(Types.SMALLINT, "smallint" );
		registerColumnType(Types.TINYINT, "tinyint" );
		registerColumnType(Types.INTEGER, "int" );
		registerColumnType(Types.CHAR, "varchar($l)" );
		registerColumnType(Types.VARCHAR, "varchar($l)" );
		registerColumnType(Types.FLOAT, "float" );
		registerColumnType(Types.DOUBLE, "numeric($p,$s)" );
		registerColumnType(Types.REAL, "numeric($p,$s)" );
		registerColumnType(Types.DATE, "datetime" );
		registerColumnType(Types.TIME, "datetime" );
		registerColumnType(Types.TIMESTAMP, "datetime" );
		registerColumnType(Types.VARBINARY, "image" );		
		registerColumnType(Types.NUMERIC, "numeric($p,$s)" );
		registerColumnType(Types.BLOB, "image" );
		registerColumnType(Types.CLOB, "text" );
		registerColumnType(Types.LONGVARCHAR, "text" );
		registerColumnType(Types.LONGVARBINARY, "image" );

		// ----- register functions -------------------------------------------
		
		// Special functions
		registerFunction("sql_calc_found_rows", "");
		registerFunction("rownumber", "row_number()");
		//registerFunction("rownumber", "");
		registerFunction("over", "OVER ($1)");
		registerFunction("connection_id", "(SELECT @@SPID)");
		
		// Convert functions		
		registerFunction("convert", "CONVERT($2 ,$1)");
		registerFunction("text_to_varchar", "CONVERT(varchar(2048) ,$1)");
		registerFunction("to_varchar", "CONVERT(varchar(256) ,$1)");
		registerFunction("to_number", "$1");
		registerFunction("to_date", "$1");
		registerFunction("to_timestamp", "$1");
		registerFunction("date_to_varchar", "CONVERT(varchar, $1, 120)");

		// String functions
		registerFunction("left", "LEFT($1, $2)");
		registerFunction("right", "RIGHT($1, $2)");
		registerFunction("substring", "SUBSTRING($1, $2, $3)");
		registerFunction("substring_index", "SUBSTRING($1, LEN($1)+2-CHARINDEX($2,REVERSE($1)), LEN($1))"); 
		registerFunction("ltrim", "LTRIM($1)");
		registerFunction("rtrim", "RTRIM($1)");
		registerFunction("trim", "LTRIM(RTRIM($1))");
		registerFunction("lpad", "REPLACE(RIGHT(SPACE($2)+RTRIM($1), $2), ' ', $3)");
		registerFunction("rpad", "REPLACE(LEFT(RTRIM($1)+SPACE($2), $2), ' ', $3)");
		registerFunction("length", "LEN($1)");
		registerFunction("char_length", "LEN($1)");
		registerFunction("char", "CHAR($1)");
		registerFunction("upper", "UPPER($1)");
		registerFunction("lower", "LOWER($1)");

		registerFunction("concat", "($n{ + })");
		registerFunction("password", "$1");
		registerFunction("locate", "CHARINDEX($1, $2, $3)");
		registerFunction("replace", "REPLACE($1, $2, $3)");
		
		// Date & time functions
		registerFunction("now", "GETDATE()");
		registerFunction("dateadd", "DATEADD($1, $2, $3)");
		registerFunction("date_add", "DATEADD($1, $2, $3)");
		registerFunction("date_sub", "DATEADD($3, $2*(-1), $1)");
		registerFunction("date_format", "CONVERT(varchar, $1, 120) ");
		registerFunction("year", "YEAR($1) ");
		registerFunction("month", "MONTH($1) ");

		registerFunction("to_days", "CONVERT(int , $1) + 693960");
		registerFunction("datediff_day", "DATEDIFF(DAY, $1, $2)");
				
		// Logic functions
		registerFunction("if", "(CASE WHEN $1 THEN $2 ELSE $3 END)");
		registerFunction("isnull", "$1 IS NULL");
		registerFunction("not isnull", "$1 IS NOT NULL");
		registerFunction("ifnull", "(CASE WHEN $1 IS NULL THEN $2 ELSE $1 END)");
		registerFunction("if_isnull", "(CASE WHEN $1 IS NULL THEN $2 ELSE $3 END)");
		
		// Numericfunctions
		registerFunction("abs", "ABS($1)");
		registerFunction("round", "ROUND($1, $2)");

		// DBMetaData variables
		// TableMetaData variables
		// FieldMetadata variables
		FMD_COLUMN_CONSTRAINTED = true;
		FMD_APPEND_DEFAULT_IFNULL = true;
		//FMD_NULL_COLUMN_STRING = "NULL";
		
		FMD_IDENTITY_BUILD_STYLE = 1;
		FMD_IDENTITY_BUILD_PART = "IDENTITY(1, 1)";
		FMD_IDENTITY_PARSE_COLUNM_NAME = "TYPE_NAME";
		
		// PrimaryKeyMetaData variables
		PK_CONSTRAINTED = true;
		
		// IndexMetaData variables		
		// Convert Element variables
		SQL_UPDATE_JOIN_WITH_FROM = true;
		SQL_DELETE_JOIN_WITH_FROM = true;
		
		// Convert values variables
	}

	// ----- Quoted methods ---------------------------------------------------
	
	public String LQ() { return "["; }
	public String RQ() { return "]"; }
	public String CTI_LQ() { return "["; }
	public String CTI_RQ() { return "]"; }

	// ----- Database connection methods --------------------------------------
	


	// ----- Connection methods -----------------------------------------------
	
	public String getSQLConnPrefix() {
		return "jdbc:sqlserver:";
	}

	public String getSQLConnDriver() {
		String driver = super.getSQLConnDriver(); 
		if (driver != null) return driver;
		//return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		return "com.jnetdirect.jsql.JSQLDriver";
	}

	public String generateSQLConnUrl(SQLConnect sqlconn) throws SQLException {
		//System.out.println("..."+sqlconn.getProperties()); 
		//System.out.println("mssql.driver = "+sqlconn.getProperty("driver"));
		/*
		//return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		return  "com.jnetdirect.jsql.JSQLDriver";
		*/
		String driver = getSQLConnDriver();
		sqlconn.setDriver(driver);
		String url = "";
		if (driver.endsWith("JSQLDriver")) {
			url = sqlconn.getPrefix();
			String host = sqlconn.getHost();
			if (host == null) throw new SQLException("Host in URL address is .NULL.");
			url += "//"+host;
	
			String port = sqlconn.getPort();
			if (port != null){
				url += ":"+port;
			}
		} else {
			url = sqlconn.getPrefix();
			String host = sqlconn.getHost();
			if (host == null) throw new SQLException("Host in URL address is .NULL.");
			url += "//"+host;
	
			String port = sqlconn.getPort();
			if (port != null){
				url += ":"+port;
			}
			String database = sqlconn.getDatabase();
			if (database != null) {
				url += ";databaseName="+database;
			}
		}
		//System.out.println("MSSQLDialect.generateSQLConnUrl().url = "+url);
		return url;
		
	}

	public Properties parseURL(String url) {
		try {
			Properties prop = new Properties();
			
			String shl = "";
			String shr = "";
			
			if (url.startsWith("jdbc:sqlserver:")) {
				int pos = url.indexOf(";");
				if (pos > -1) {
					shl = url.substring(0, pos);
					shr = url.substring(pos+1);
				} else {
					shl = url;
				}				
				if (shl != null && shl.length() > 0) {
					pos = shl.indexOf("//");
					if (pos > -1) {
						String prefix = shl.substring(0, pos);
						shl = shl.substring(pos+2);
						prop.put("prefix", prefix);
					} 
					String host = shl;
					pos = host.indexOf(":");
					if (pos > -1) {
						String port = host.substring(pos+1);
						host = host.substring(0, pos);
						prop.put("port", port);
					}
					prop.put("host", host);
				}
				if (shr != null && shr.length() > 0) {
					StringTokenizer st = new StringTokenizer(shr,";");
					while (st.hasMoreTokens()) {
						String element = st.nextToken();
						pos = element.indexOf("=");
						if (pos > -1) {
							String key = element.substring(0, pos);
							String value = "";
							if (pos < element.length()) value = element.substring(pos+1, element.length());
							if (key.equals("database")) key = "databaseName";
							prop.put(key, value);
						}
					}
				}
				prop.put("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
	
			} else if (url.startsWith("jdbc:JSQLConnect:")) {
				int pos = url.indexOf("?");
				if (pos > -1) {
					shl = url.substring(0, pos);
					shr = url.substring(pos+1);
				} else {
					shl = url;
				}				
				if (shl != null && shl.length() > 0) {
					pos = shl.indexOf("//");
					if (pos > -1) {
						String prefix = shl.substring(0, pos);
						shl = shl.substring(pos+2);
						prop.put("prefix", prefix);
					} 
					String host = shl;
					pos = host.indexOf(":");
					if (pos > -1) {
						String port = host.substring(pos+1);
						host = host.substring(0, pos);
						prop.put("port", port);
					}
					prop.put("host", host);
				}
				if (shr != null && shr.length() > 0) {
					StringTokenizer st = new StringTokenizer(shr,"&");
					while (st.hasMoreTokens()) {
						String element = st.nextToken();
						pos = element.indexOf("=");
						if (pos > -1) {
							String key = element.substring(0, pos);
							String value = "";
							if (pos < element.length()) value = element.substring(pos+1, element.length());
							prop.put(key, value);
						}
					}
				}
				prop.put("driver", "com.jnetdirect.jsql.JSQLDriver");
			}
			return prop;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getDefaultSchema()
	 */
	public String getDefaultCatalog() {
		return "dbo";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getDefaultSchema()
	 */
	public String getDefaultSchema() {
		return "dbo";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getSchema()
	 */
	public String getSchema() {
		if (super.getSchema() != null) return super.getSchema(); 
		else return getDefaultSchema();
	}

	public synchronized Statement createStatement(Connection conn) throws SQLException {
		return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 	
	}

	public synchronized Statement createStatement(Connection conn, int style) throws SQLException {
		int concur = ResultSet.CONCUR_READ_ONLY;
		switch (style) {
			case 0: concur = ResultSet.CONCUR_READ_ONLY; break;
			case 1: concur = ResultSet.CONCUR_UPDATABLE; break;
			default:
				break;
		}
		return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, concur); 	
	}
	
	// ----- ColumnType methods -----------------------------------------------
	
	// ----- ReservedKeywords methods -----------------------------------------
	
	// ----- Fn Methods -------------------------------------------------------
	
	//* ***********************************************************************
	//* Database Mapping methods
	//* ***********************************************************************
	
	// ----- DBMetaData methods -----------------------------------------------
	
	// ----- TableMetaData metdods --------------------------------------------
	
	// ----- FieldMetadata methods --------------------------------------------
	
	public String getColumnTypeString(int sqltype, int length, int scale) {		
		if (sqltype == Types.CHAR && length > 1) {
			sqltype = Types.VARCHAR;
		}
		String s = super.getColumnTypeString(sqltype, length, scale);
		return s;
	}

	public String getColumnConstraintKey(String table, String column) {
		String key = "DF_"+table.toLowerCase()+"_"+column.toUpperCase();
		return key;
	}

	public boolean isColumnConstrainted() { 
		return FMD_COLUMN_CONSTRAINTED; 
	}
	
	//public String getNullColumnString() { return "NULL"; }

	public String prepareDefaultValue(String source) {
		String s = "";
		if (source == null) {
			s = source;
		} else if (source.matches("\\(\\'(.*)\\'\\)")) {
			s = source.substring(2, source.lastIndexOf("'"));
		} else {
			s = source;
		}
		return s;
	}

	public boolean isIdentityColumn(String value) {
		if (value == null || value.length() == 0) return false;
		return (value.toUpperCase().indexOf("IDENTITY") > -1);
	}
	
	// ----- PrimaryKey Constraint --------------------------------------------
	/*
	public boolean isPKConstrainted() { 
		return true; 
	}
	*/
	public String getPKConstraintKey(String table, String column) {
		String key = "PK_"+table;
		return key;
	}
	// ----- IndexMetaData methods --------------------------------------------

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateIndexName(java.lang.String, java.lang.String, java.lang.String)
	 */
	public String generateIndexName(String tableName, String indexName, String sufix) {
		String name = tableName.toLowerCase()+"_idx"+"_"+indexName.toLowerCase();
		return name;
	}

	// ----- Connection methods -----------------------------------------------

	/*
	public boolean isUpdateJoinWithFrom() {
		return true;
	}
	*/
	/*
	public boolean isDeleteJoinWithFrom() {
		return true;
	}
	*/
	// ----- Convert Values methods -------------------------------------------
	
	public String getValueInteger(String value) {
		return ""+value+"";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#showProcessList()
	 */
	public String getValueString(String value, int length) {
		if (value.matches("\\s+")) {
			value = value.trim();
		} else {
			value = this.text2SQL(value);
		}
		if (value.length() > length) {
			value = value.substring(0, length);
		}
		return "'"+value+"'";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#showProcessList()
	 */	
	public String getValueString(String value) {
		if (value.matches("\\s+")) {
			value = "'"+value.trim()+"'";
		} else {
			value = "'"+this.text2SQL(value)+"'";
		}
		return value;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#showProcessList()
	 */
	public String getValueBlob(String value) {
		if (value.matches("\\s+")) {
			value = "'"+value.trim()+"'";
		} else {
			value = "'"+this.text2SQL(value)+"'";
		}
		return value;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#showProcessList()
	 */	
	public String getValueClob(String value) {
		if (value.matches("\\s+")) {
			value = "'"+value.trim()+"'";
		} else {
			value = "'"+this.text2SQL(value)+"'";
		}
		return value;
	}

	public String text2SQL(String input)  {
		StringBuffer buf = new StringBuffer("");
		for (int i = 0; i < input.length(); i++) {
			if (input.charAt(i) == '\\') {
				buf.append("\\\\");
			} else if (input.charAt(i) == '\'') {
				buf.append("\'\'");
			} else {
				buf.append(input.charAt(i));
			}
		} return buf.toString();
	}

	// ----- DDL methods ------------------------------------------------------
	/*
	public String getCreateTableString(TableMetaData tbmd) {
		String s = "CREATE TABLE ["+this.getSchema()+"].["+tbmd.getName()+"] (\n";
		for (int i = 0; i < tbmd.getFields().size(); i++) {
			FieldMetaData fmd = (FieldMetaData)tbmd.getFields().elementAt(i);
			//s += (String)getFields().get(i);
			s += fmd.toString();
			s += (i < tbmd.getFields().size()-1) ? ",\n" : "\n"; 
		}
		PrimaryKeyMetaData pkmd = tbmd.getPrimaryKey();
		if (pkmd != null) {
			String columnname = pkmd.getColumn();
			s += "PRIMARY KEY CLUSTERED (["+columnname+"]) CONSTRAINT [PK_"+tbmd.getName()+"_"+columnname+"]";
		}
		s += ")";
		return s;		
	}
	*/
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableFieldString(com.smart4web.db.structure.FieldMetaData)
	 */
	public String getCreateTableFieldString(FieldMetaData fmd) {
		String s = "";
	    String tableName = fmd.getTable();
		String columnName = (isKeyword(fmd.getName())) ? 
			this.LQ() + (fmd.getName()+"_") + this.RQ() : 
			this.LQ() + fmd.getName() + this.RQ();
			
		if (fmd.isPrefix()) s += this.LQ() + tableName + this.RQ() + ".";
		s +=  columnName;    
		fmd.setTypeName(this.getColumnTypeString(
		    fmd.getSqlType(), fmd.getSize(), fmd.getDecimal())
		);
		s += " " + fmd.getTypeName() + " ";
	
		if (fmd.isNullable()) {
			if (fmd.hasValue()) {
				s += "DEFAULT " + fmd.getValue() + " ";
			}
			s += this.getNullColumnString();
		} else {
			s += "DEFAULT " + fmd.getValue() + " ";
			s += " NOT NULL ";
		}
		if (this.isColumnConstrainted()) {
			//CONSTRAINT [DF__acc_day_bo__TYPE__575F7FA5]
			s += " CONSTRAINT ["+this.getColumnConstraintKey(tableName, columnName)+"]";
		}
		return s;
	}

	public String getDropTableString(String schema, String table) {
		return "DROP TABLE ["+this.getSchema()+"].["+table+"]";
	}

	public String getCreateIndexesString(IndexesMetaData ixmd) {
		Vector<String> list = (Vector<String>)getCreateIndexesList(ixmd);
		String s = "";
		for (Enumeration<String> e = list.elements() ; e.hasMoreElements() ;) {
			String query = e.nextElement();
			s += query + ((e.hasMoreElements()) ? "; " : "");
		}	 		
		return s;
	}

	public String getDropIndexString(String tableName, String indexName) {
		return "DROP INDEX ["+tableName+"] ON [dbo].["+indexName+"]";
	}
	
	public List<String> getCreateIndexesList(IndexesMetaData ixmd) {
		Vector<String> list = new Vector<String>();
		for (Enumeration e = ixmd.elements(); e.hasMoreElements();) {
			Hashtable params = (Hashtable)e.nextElement();
			Hashtable columns = (Hashtable)params.get("columns");
			
			String name = (String)params.get("INDEX_NAME");
			//System.out.println("INDEX_NAME = "+name);	
			String query = "";
			if (name.equals("PRIMARY")) {
			} else {
				//String asc_desc = (String)params.get("ASC_OR_DESC");
				boolean nonunique = ((Boolean)params.get("NON_UNIQUE")).booleanValue();
				
				String colstr = "( ";
				for (int i = 0; i < columns.size(); i++) {
					String columnname = (String)columns.get(new Integer(i+1));
					//columnname = (ReservedKeywords.isReservedKeyword(columnname)) ? (columnname+"_") : columnname;
					colstr += LQ() + columnname + RQ(); 					
					if (i < columns.size()-1) colstr += ",";
				}
				colstr += " )";
								
				query = "CREATE ";
				if (!nonunique) { 
					query += "UNIQUE ";
				}
				// query += asc_desc;
				query += "INDEX "+name+" ON ["+this.getSchema()+"].["+ixmd.getTable()+"] "+colstr;
				list.add(query);
			}
		}
		return list;
	}
	
	/**
 	 * Metoda provede vygerov�n� SQL dotaz� i samostn� z�pis do DB.
	 *  
	 * @param alterTable - objekt typu AlterTable.
	 * @param conn
	 * @return
	 */
	public synchronized void execAlterTable(AlterTable alterTable, Connection conn) throws Exception {
		Statement stmt = null;		
		try {
			Dialect d = alterTable.getDialect();
	
			String schema = d.getSchema();
			String actionType = alterTable.getActionType();
			String table = alterTable.getTableName();
			String[] query = new String[0];
			
			String contraintKey = "";
			if (actionType.equals(AlterTable.ADD_INDEX) || actionType.equals(AlterTable.DROP_INDEX)) {
				Map<String, Object> map = alterTable.getIndex();
				Map<Integer, String> columns = (Map<Integer, String>)map.get("columns");
				String columnName = columns.get(1);
				contraintKey = table.toLowerCase()+"_idx"+"_"+columnName.toLowerCase();
				query = new String[1];
				if (actionType.equals(AlterTable.ADD_INDEX)) {
					//query[0] = "ALTER TABLE "+table+" ADD INDEX ("+columnName+")";
					query[0] = "CREATE INDEX ["+contraintKey+"] ON "+
						((schema != null) ? "["+schema+"]." : "")+"["+table+"] ([" + columnName + "])";
				} else if (actionType.equals(AlterTable.DROP_INDEX)) {
					//query[0] = "ALTER TABLE "+table+" DROP INDEX ("+columnName+")";
					query[0] = "DROP INDEX ["+contraintKey+"] ON "+((schema != null) ? "["+schema+"]." : "")+"["+table+"]";
				}
			} else if (actionType.equals(AlterTable.ADD_PRIMARY_KEY) || actionType.equals(AlterTable.DROP_PRIMARY_KEY)) {
				TableMetaData tbmd = alterTable.getDBMetaData().getTable(table);
				PrimaryKeyMetaData pkmd = tbmd.getPrimaryKey();
				
				String column = pkmd.getColumn();
				String tableStr = d.LQ() + schema +d.RQ() + "."+d.LQ() + table + d.RQ();
				String columnStr = d.LQ() + column + d.RQ();
				String constrait = this.getPKConstraintKey(table, column);
				
				query = new String[1];
				if (actionType.equals(AlterTable.ADD_PRIMARY_KEY)) {
					query[0] = 
					"ALTER TABLE " + tableStr + " WITH NOCHECK " + 
						"ADD CONSTRAINT " + constrait + " " +
						"PRIMARY KEY CLUSTERED (" + columnStr + ")";
				} else {
					query[0] = "ALTER TABLE " + tableStr + " WITH NOCHECK " + "DROP CONSTRAINT " + constrait + "";					
				}
			} else {
				TableMetaData tbmd = alterTable.getDBMetaData().getTable(table);
				FieldMetaData fmd = alterTable.fmd();
							
				String column = fmd.getName();
				String tableStr = d.LQ() + schema +d.RQ() + "."+d.LQ() + table + d.RQ();
				String columnStr = d.LQ() + column + d.RQ();
				String constrait = getColumnConstraintKey(table, column);
		
				if (actionType.equals(AlterTable.ADD_COLUMN)) {
					fmd.setTypeName(this.getColumnTypeString(fmd.getSqlType(), fmd.getSize(), fmd.getDecimal()));
					
					query = new String[1];
					query[0] = 
					"ALTER TABLE "+tableStr+" "+
						"ADD " + columnStr + " " + fmd.getTypeName() + " " +
						"CONSTRAINT " + d.LQ() + constrait + d.RQ() + " " + 
						"DEFAULT " + fmd.getValue() + 
						((fmd.isNullable()) ? " NULL" : " NOT NULL");
					/*
					query[1] = "ALTER TABLE " + tableStr + " " +
						"ADD CONSTRAINT "+d.LQ()+constrait+d.RQ()+" "+ "DEFAULT "+fmd.getValue()+" FOR "+columnStr;
					*/
				} else if (actionType.equals(AlterTable.MODIFY_COLUMN) || actionType.equals(AlterTable.CHANGE_COLUMN)) {
					//System.out.println("MSSQLDialect.execAlterTable( MODIFY_COLUMN )");
					fmd.setTypeName(this.getColumnTypeString(fmd.getSqlType(), fmd.getSize(), fmd.getDecimal()));
					
					query = new String[5]; 
					int j = 0;
					
					String constraint = "DF_"+table.toLowerCase()+"_"+column.toUpperCase(); 
					if (constraintExists(constraint, conn)) {						
						query[j] = "ALTER TABLE " + tableStr + " DROP CONSTRAINT "+ d.LQ() + constraint + d.RQ();				                   
						j++;
					} else {
						constraint = constraintName(table.toLowerCase(), column.toUpperCase(), conn);
						if (constraint != null) {
							query[j] = "ALTER TABLE " + tableStr + " DROP CONSTRAINT "+ d.LQ() + constraint + d.RQ();				                   
							j++;
						}
					}
					boolean hasIndex = false;
					String indexName = table.toLowerCase()+"_idx_"+column.toLowerCase();
					if (tbmd.getIndexes().containsKey(indexName)) {
						hasIndex = true;				
						query[1] = "DROP INDEX " +d.LQ() + indexName + d.RQ() + " ON " + tableStr;
						j++;
					}				
					query[j] = "ALTER TABLE " + tableStr + " " +
						"ALTER COLUMN " + columnStr + " " + fmd.getTypeName() + ((fmd.isNullable()) ? " NULL" : " NOT NULL");	
					j++;
					query[j] = "ALTER TABLE " + tableStr + " " +
						"ADD CONSTRAINT " + d.LQ() + constrait + d.RQ() + " " + "DEFAULT " + fmd.getValue() + " FOR " + columnStr;
					j++;
					if (hasIndex) {
						query[j] = "CREATE INDEX ["+indexName+"] ON "+tableStr+" ("+columnStr+")";	
						j++;
					}
				} else if (actionType.equals(AlterTable.DROP_COLUMN)) {
					
					query = new String[3];
					int j = 0;
					String key = table.toLowerCase()+"_idx_"+column.toLowerCase();
					if (tbmd.getIndexes().containsKey(key)) { 	
						query[j] = "DROP INDEX " + d.LQ() + key + d.RQ()+ " ON " + tableStr;
						j++;
					}
					String constraint = this.getColumnConstraintKey(table, column);
					query[j] = "ALTER TABLE " + tableStr + " " + "DROP CONSTRAINT "+ d.LQ() + constraint + d.RQ();				                   
					j++;
					query[j] = "ALTER TABLE " + tableStr + " " + "DROP COLUMN " + columnStr;
				}
			}
			stmt = conn.createStatement();
			for (int i = 0; i < query.length; i++) {
				if (query[i] != null) {
					//System.out.println("MSSQLDialect.exec.query[i]:\n"+query[i]);
					try {
						stmt.execute(query[i]);	
					} catch (Exception exc) {
						//exc.printStackTrace();
						System.out.println("[ERROR] MSSQLDialect.execAlterTable(): "+exc.getMessage()+"\n\t"+query[i]);
						throw exc;
					}						
				}
			}
		} catch (Exception exc) {
			//exc.printStackTrace();
			throw exc;
		} finally {
			try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
		} 
	}

	/**
 	 * Metoda vrac� jm�no constraitu, kter� najde v systemov�ch tabulk�ch podle 
 	 * prametr� tableName a columnName. Pokud constraint neexistuje vrac� null.  
	 * 
	 * @param tableName - jmeno DB tabulky. 
	 * @param columnName - jmeno sloupce v DB tabulce.
	 * @param conn
	 * @return
	 */
	private String constraintName(String tableName, String columnName, Connection conn) {
		String constraint = null; 
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT t.NAME [TABLE], c.NAME [COLUMN], d.NAME [CONSTRAINT] " +
			"FROM sys.default_constraints d " +
			"JOIN sys.tables t ON t.object_id = d.parent_object_id " +
			"JOIN sys.columns c ON c.object_id = t.object_id AND	c.column_id = d.parent_column_id " +
			"WHERE t.NAME = '"+tableName+"' AND c.NAME = '"+columnName+"' " +
			"";
			stmt = createStatement(conn);
			rs = stmt.executeQuery(sql);
			if (rs.first()) {
				constraint = rs.getString("CONSTRAINT");
			}
		} catch (Exception exc) {
			exc.printStackTrace();
			constraint = null;
		} finally {
			try { if (rs != null) rs.close(); } catch (SQLException e) {}
			try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
		}
		return constraint;
	}

	/**
 	 * Metoda zjist� existenci hledaneho constraintu v systemov�ch tabulk�ch.  
	 * 
	 * @param constraint
	 * @param conn
	 * @return
	 */
	private boolean constraintExists(String constraint, Connection conn) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String query = "" +
			"SELECT OBJECT_NAME(OBJECT_ID) AS CONSTRAINT_UID, " +
			"SCHEMA_NAME(schema_id) AS SCHEMA_UID, " +
			"OBJECT_NAME(parent_object_id) AS TABLE_NAME, " +
			"type_desc AS CONSTRAINT_TYPE " +
			"FROM sys.objects " +
			"WHERE type_desc LIKE '%CONSTRAINT' " + 
			"AND OBJECT_NAME(OBJECT_ID) = '" + constraint + "'";

			stmt = createStatement(conn);
			rs = stmt.executeQuery(query);
			if (rs.first()) {
				return true;
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (SQLException e) {}
			try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
		}
		return false;
	}
	
	// ----- LOCK & UNLOCK TABLE methtods -------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#lockTable(java.lang.String)
	 */
	public String lockTable(String table) {
		return "begin transaction;";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#unlockTable(java.lang.String)
	 */
	public String unlockTable(String table) {
		return "commit transaction;"; 
	}

	// ----- QueryRebuilder methods -------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateLimitQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized String generateLimitQuery(SelectQuery query) throws Exception {
		//System.out.println("MSSQLDialect.generateLimitQuery():\n"+query.translate());
		//Object alias = (Object)query.getAliasMap().get("rownum");
		boolean exec = true;
		if (query.getAliasMap() != null) {
			exec = !query.getAliasMap().containsKey("rownum");
		}
		//System.out.println("MSSQLDialect.generateLimitQuery().exec = "+exec);
		if (exec) {
			try {
				SQLSpecFunc fn = new SQLSpecFunc("rownumber");
				fn.setDialect(this);
				UniQLOrderBy orderBy = null;
				if (query.getOrderBy() != null) orderBy = (UniQLOrderBy)query.getOrderBy().clone();
				//System.out.println("MSSQLDialect.Limit(0).orderBy = "+orderBy);
				
				if (orderBy == null) {
					//System.out.println("MSSQLDialect.Limit(0)");
					String table = (String)query.getAliasMap().get("mainFrom");
					orderBy = new UniQLOrderBy(this);
					orderBy.addExpr(((table != null) ? table+"." : "")+"IDU", "ASC");
				} else {
					orderBy.setAliasMap(query.getAliasMap());
				}
				if (!orderBy.getColumns().isEmpty()) {
					if (query.getOrderBy() != null) query.getOrderBy().clear();
					HashMap<String, Object> aliasMap = query.getAliasMap();
					ArrayList<UniQLOrderByElement> list = orderBy.getColumns();
					for (int j = 0; j < list.size(); j++) {
						//HashMap map = (HashMap)list.get(j);
						UniQLOrderByElement element = list.get(j);
						UniQLExpr expr = element.getExpr();
						Integer type = element.getType();
						if (type == null) {
							type = new Integer(0);
						}
						if (expr instanceof UniQLColumn) {
							UniQLColumn column = (UniQLColumn)expr;
							String alias = column.getAlias();
							if (alias == null) {
								alias = column.getTableName();
								if (alias == null) {
									alias = (String)aliasMap.get("mainFrom");									
								}
							}
							if (alias != null) {
								String table = (String)aliasMap.get(""+alias.toLowerCase());
								if (table == null) table = (String)aliasMap.get("mainFrom");
								String colName = column.getName();
								//System.out.println("this.dbmd()" + this.dbmd());
								
								TableMetaData tbmd = query.dbmd().getTable(table);
								if (tbmd != null) {
									FieldMetaData fmd = tbmd.getFieldByName(""+colName.toUpperCase());
									if (fmd != null) {
										int sqltype = fmd.getSqlType();
										switch (sqltype) {
											case Types.LONGVARCHAR: {
												expr = new SQLFunc("text_to_varchar").addParam(column);
												break;
											}
										}
									}					
								}
							} 
							query.addOrderBy(expr, type.intValue());
						} if (expr instanceof SQLFunc) {
							query.addOrderBy(expr, type.intValue());
						}
					}
				}
				fn.addSufix(new SQLFunc("over").addParam(query.getOrderBy()));
				UniQLField field = new UniQLField(fn, "rownum");
				field.setDialect(this);
				query.addField(field);				
			} catch (Exception exc) {
				exc.printStackTrace();
			}
			//System.out.println("MSSQLDialect.generateLimitQuery().aliasMap = "+query.getAliasMap());
		}			
		UniQLLimit limit = query.getLimit();
		int first = limit.getFirstResult();
		int count = limit.getCountResult();
		query.removeOrderBy();
		String s = "SELECT * FROM (\n";
		s += query.translate()+"\n";
		s += ") AS A \n";
		//s += "WHERE A.rownum BETWEEN ("+first+") AND ("+first+" + ("+count+"-1))";
		s += "WHERE A.rownum BETWEEN ("+first+" + 1) AND ("+first+" + ("+count+"))";
		return s;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateRowCountQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized SelectQuery generateRowCountQuery(SelectQuery query) throws Exception {
		try {
			SelectQuery sql = (SelectQuery)query.clone();
			sql.removeOrderBy();
			ArrayList<UniQLExpr> fields = sql.getFields();
			fields.clear();
			sql.removeLimit();
			boolean exec = true;
			if (sql.getAliasMap() != null) {
				exec = !sql.getAliasMap().containsKey("rownum");
			}
			if (exec) {
				SQLSpecFunc fn = new SQLSpecFunc("rownumber");
				fn.setDialect(this);
				UniQLOrderBy orderBy = sql.getOrderBy();
				if (orderBy == null) {
					String table = (String)query.getAliasMap().get("mainFrom");
					orderBy = new UniQLOrderBy(this);
					orderBy.addExpr(((table != null) ? table+"." : "")+"IDU", "ASC");
				} else {
					orderBy.setAliasMap(query.getAliasMap());
				}
				fn.addSufix(new SQLFunc("over").addParam(orderBy));
				UniQLField field = new UniQLField(fn, "rownum");
				field.setDialect(this);
				sql.addField(field);	
			}
			if (sql.getGroupBy() != null) {
				if (sql.getGroupBy() != null && this.isGroupByRewrite()) {
					if (sql.getGroupBy().isQueryRewritable()) {
						sql = this.generateGroupByQuery(sql);
					}
				}
			}
			sql.removeOrderBy();
//$			
			UniQLSubQuery subquery = new UniQLSubQuery(this, null);
			subquery.setSelect(sql);
			subquery.setAlias("A");
			query.clear();
			query.addField(new SQLAgregFunc("COUNT").addParam("A.rownum"), "ROW_COUNT"); 
			query.addFrom(subquery);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query;
	}
		

	// ----- GroubBy method ---------------------------------------------------
	/*
	private Map<String, String> getWhereAliasMap(UniQLWhere where) throws Exception {
		return getWhereAliasMap(where, null);
	}
	*/
	private Map<String, String> getWhereAliasMap(UniQLWhere where, Map<String, String> map) throws Exception {
		if (map == null) map = new LinkedHashMap<String, String>();
		if (where.getCriterions() != null) {
			ArrayList<UniQLCriterionElement> crierions = where.getCriterions().getConditions();
			int count = crierions.size();
			for (int j = 0; j < count; j++) {
				//HashMap crimap = (HashMap)crierions.get(j);
				UniQLCriterionElement element = (UniQLCriterionElement)crierions.get(j);
				//UniQLExpr expr = (UniQLExpr)crimap.get("condition");
				UniQLExpr expr = (UniQLExpr)element.getCondition();
				if (expr instanceof UniQLCondition) {
					UniQLCondition cond = (UniQLCondition)expr;
					UniQLExpr exprLeft = cond.getLeftFragment();
					UniQLExpr exprRight = cond.getRightFragment();
					if (exprLeft != null) {
						if (exprLeft instanceof UniQLColumn) {
							String tblName = ((UniQLColumn)exprLeft).getTableName();
							if (tblName != null) {
								map.put(tblName.toLowerCase(), tblName.toLowerCase());
							}
						}
					}
					if (exprRight != null) {
						if (exprRight instanceof UniQLColumn) {
							String tblName = ((UniQLColumn)exprRight).getTableName();
							if (tblName != null) {
								map.put(tblName, tblName);
							}
						}
					}
				} else if (expr instanceof UniQLConditionGroup) {
					UniQLConditionGroup group = (UniQLConditionGroup)expr;
					getWhereAliasMap(group.getConditions(), map);
				}
			}
		}
		return map;
	}

	private Map<String, String> getWhereAliasMap(ArrayList<UniQLCriterionElement> crierions, Map<String, String> map) throws Exception {
		int count = crierions.size();
		for (int j = 0; j < count; j++) {
			//HashMap crimap = (HashMap)crierions.get(j);
			UniQLCriterionElement element = (UniQLCriterionElement)crierions.get(j); 
			UniQLExpr expr = (UniQLExpr)element.getCondition();
			if (expr instanceof UniQLCondition) {
				UniQLCondition cond = (UniQLCondition)expr;
				UniQLExpr exprLeft = cond.getLeftFragment();
				UniQLExpr exprRight = cond.getRightFragment();
				if (exprLeft != null) {
					if (exprLeft instanceof UniQLColumn) {
						String tblName = ((UniQLColumn)exprLeft).getTableName();
						if (tblName != null) {
							map.put(tblName, tblName);
						}
					}
				}
				if (exprRight != null) {
					if (exprRight instanceof UniQLColumn) {
						String tblName = ((UniQLColumn)exprRight).getTableName();
						if (tblName != null) {
							map.put(tblName, tblName);
						}
					}
				}
			} else if (expr instanceof UniQLConditionGroup) {
				//UniQLConditionGroup group = (UniQLConditionGroup)expr;
			}
		}
		return map;
	}

	private boolean generateGroupByQueryByColumn(SelectQuery query, UniQLSubQuery subquery, UniQLExpr expr, UniQLExpr column, String table, Map<String, String> whereAliasMap) throws Exception { 
		boolean result = false; 
		HashMap<String, Object> map = query.getAliasMap();
		//UniQLGroupBy groupBy = query.getGroupBy();
		//ArrayList<UniQLExpr> list = groupBy.getExprList();
		ArrayList<UniQLExpr> fields = query.getFields();
 				
		//String fromName = (String)map.get("mainFrom");
		HashMap<String, Object> fromIndex = query.getFromAliasMap(); 	//(HashMap)map.get("fromIndex");
		HashMap<String, Object> joinIndex = query.getJoinAliasMap();	//(HashMap)map.get("joinIndex");
		
		if (fromIndex.containsKey(table)) {
			expr = (UniQLExpr)fromIndex.get(table);
		} else {
			expr = (UniQLExpr)((ArrayList<UniQLExpr>)query.getTables()).get(0);
		}
		if (expr != null) {
			String repl = (String)map.get(table);
			if (subquery.getSelect().getTables().isEmpty()) {
				subquery.getSelect().addFrom((repl != null) ? repl : table);
			} else {
				//((HashMap<String, Object>)subquery.getSelect().getAliasMap().get("fromIndex")).get("");
			}
			subquery.getSelect().addGroupBy(column);
			if (!subquery.getSelect().getAliasMap().containsKey("GROUP_UID")) {
				subquery.getSelect().addField(
				    new UniQLField(
				 		new SQLFunc("MAX").addParam(new UniQLColumn(table+".IDU"))
				 	).as("GROUP_UID")
				);
			}
			int fldcnt = fields.size();
			for (int j = fldcnt-1; j > -1; j--) {
				UniQLExpr field = ((UniQLExpr)fields.get(j));
				if (field instanceof UniQLField) {
					UniQLExpr fn = ((UniQLField)field).getExpr();
					if (fn instanceof SQLAgregFunc) {
						subquery.getSelect().addField(field);
						query.removeField(j);
					} else if (fn instanceof SQLFunc) {
						SQLFunc fn0 = (SQLFunc)fn;
						boolean exec = false;
						for (Iterator<Object> iterator = fn0.getParameters().iterator(); iterator.hasNext();) {
							Object param = iterator.next();
							if (param instanceof SQLAgregFunc) {
								exec = true;
								break;
							} else if (param instanceof UniQLCondition) {
								UniQLCondition c = (UniQLCondition)param;
								if (c.getLeftFragment() instanceof SQLAgregFunc) {
									exec = true;
									break;									
								}
								if (c.getRightFragment() instanceof SQLAgregFunc) {
									exec = true;
									break;									
								}
							}
						}
						if (exec) {
							subquery.getSelect().addField(field);
							query.removeField(j);							
						}
					}
				}
			}
			UniQLWhere where = query.getWhere();
			if (where != null && !where.isEmpty()) {
				whereAliasMap = getWhereAliasMap(where, whereAliasMap);
				LinkedHashMap<String, String> joinAliasMap = new LinkedHashMap<String, String>();
				for (Iterator<UniQLExpr> iterator = query.getJoins().iterator(); iterator.hasNext();) {
					UniQLExpr x = iterator.next();
					if (x instanceof UniQLJoin) {
						UniQLJoin join = (UniQLJoin) x;
						UniQLExpr from = join.getFrom();
						if (from instanceof UniQLFrom) {
							String name = ((UniQLFrom)from).getAlias();
							if (name != null) name = name.toLowerCase();
							if (name == null || !joinIndex.containsKey(name)) {
								name = ((UniQLFrom)from).getName();
							}
							joinAliasMap.put(name, name);
						}
					}  
				}
				for (Iterator<String> iterator = joinAliasMap.keySet().iterator(); iterator.hasNext();) {
					String key = (String) iterator.next();
					if (!whereAliasMap.containsKey(key)) continue;
					if (!key.equals("fromName") && !fromIndex.containsKey(key) && joinIndex.containsKey(key)) {
						UniQLJoin join = (UniQLJoin)joinIndex.get(key);
						if (join != null) {
							subquery.getSelect().addJoin(join); 
						}
					}
				}
				subquery.getSelect().setWhere(where); 
			}			
			String xml = subquery.transToXML();
			
			xml = StringUtils.replace(xml, "\""+table+"\"", "\""+repl+"\"");
			try {
				Document doc = DocumentHelper.parseText(xml);
				Element node = doc.getRootElement();
				subquery = (UniQLSubQuery)subquery.xmlToExpr(node);
			} catch (Exception e) {
				System.out.println(e.getMessage());
			} 
			subquery.as("group_alias");

			query.removeFrom(0);
			query.addFrom(subquery);
			query.getGroupBy().removeExpr(column);
			
			if (!fromIndex.containsKey("group_alias")) {
				UniQLJoin join = new UniQLJoin()
				.addFrom(expr)
				.addCondition(new UniQLCondition("group_alias.GROUP_UID", "=", table+".IDU"));
				
				query.addJoin(0, join);
				query.addField("group_alias.*");
				joinIndex.put(table, join);
				fromIndex.put("group_alias", subquery);							
			}
			result = true;
		}
		return result;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateGroupByQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized SelectQuery generateGroupByQuery(SelectQuery query) throws Exception {
		boolean exec = false;
		for (Iterator<UniQLExpr> iterator = query.getFields().iterator(); iterator.hasNext();) {
			UniQLExpr expr = iterator.next();
			if (expr instanceof UniQLField) {
				UniQLField field = (UniQLField)expr; 
				if (!(field.getExpr() instanceof SQLAgregFunc)) {
					exec = true;
					break;
				}
			} else if (expr instanceof SQLAgregFunc) {
				continue;
			} else {
				exec = true;
				break;				
			}
		}
		if (!exec) return query;
		
		String table = "";
		String alias = "";
		
		HashMap<String, Object> map = query.getAliasMap();
		UniQLGroupBy groupBy = query.getGroupBy();
		ArrayList<UniQLExpr> list = groupBy.getExprList();
		
		ArrayList<UniQLExpr> fields = query.getFields();
//$		
		UniQLSubQuery subquery = new UniQLSubQuery(this, null); 
				
		String fromName = (String)map.get("mainFrom");
		HashMap<String, Object> fromIndex = query.getFromAliasMap();
		HashMap<String, Object> joinIndex = query.getJoinAliasMap();
		if (fromIndex == null) {
			fromIndex = new HashMap<String, Object>();
			map.put("fromIndex", fromIndex);
		}
		if (joinIndex == null) {
			joinIndex = new HashMap<String, Object>();
			map.put("joinIndex", joinIndex);
		}
		boolean remove = false;
		UniQLExpr expr = null;
		int grpcount = list.size();
		if (fromIndex.containsKey("group_alias")) {
			fromIndex.remove("group_alias");
		}
		Map<String, String> whereAliasMap = new LinkedHashMap<String, String>();
		
		for (int i = grpcount-1; i > -1; i--) {
			Object obj = list.get(i);
			if (obj instanceof UniQLColumn) {
				UniQLColumn column = (UniQLColumn)obj;
				table = column.getTableName();
				if (table != null && table.length() > 0) {
					table = table.toLowerCase();
					if (alias == null) alias = table;
					if (table.equals(fromName)) {				
						remove = generateGroupByQueryByColumn(query, subquery, expr, column, table, whereAliasMap);
					} else {
						if (joinIndex.containsKey(table)) {
							String repl = (String)map.get(table);
							Set<String> set = new HashSet<String>();
							Set<String> joinedSet = new HashSet<String>();
							subquery.getSelect().addFrom((repl != null) ? repl : table);
							subquery.getSelect().addGroupBy(column);
							expr = (UniQLExpr)joinIndex.get(table);							
							fields = query.getFields();							
							int fldcnt = fields.size();
							for (int j = fldcnt-1; j > -1; j--) {
								UniQLExpr field = ((UniQLExpr)fields.get(j));
								if (field instanceof UniQLField) {
									UniQLExpr fn = ((UniQLField)field).getExpr();
									if (fn instanceof SQLAgregFunc) {
										subquery.getSelect().addField(field);
										query.removeField(j);
									}
								} else if (field instanceof UniQLColumn) {
									UniQLColumn fldColumn = (UniQLColumn)field;
									String fldAlias = fldColumn.getAlias(); 
									String tabName = fldColumn.getTableName();
									String colName = fldColumn.getName();
									if ((fldAlias != null && fldAlias.equals(table)) || (tabName != null && tabName.equals(table))) {
										fldColumn.setAlias(null); 
										SQLAgregFunc fn = new SQLAgregFunc("MAX");
										fn.addParam(fldColumn);
										subquery.getSelect().addField(fn, (fldAlias != null) ? fldAlias : colName);
										query.removeField(j);
										set.add(colName+"."+tabName);
									}	
								}
								UniQLJoin join = (UniQLJoin)expr;
								join.addFrom(subquery);
								query.getGroupBy().removeExpr(column);
								joinedSet.add(table);

								for (Iterator<UniQLCriterionElement> iterator = join.getCriterions().getConditionIterator(); iterator.hasNext();) {
									UniQLExpr exp = (UniQLExpr)((UniQLCriterionElement)iterator.next()).getCondition();
									if (exp instanceof UniQLCondition) {
										UniQLCondition c = (UniQLCondition) exp;
										UniQLExpr expr1 = c.getLeftFragment();
										UniQLExpr expr2 = c.getRightFragment();
										if (expr1 instanceof UniQLColumn) {
											UniQLColumn fldColumn = (UniQLColumn)c.getLeftFragment(); 
											String tabName = fldColumn.getTableName();
											String colName = fldColumn.getName();
											if (!set.contains(tabName + "." + colName))
											if ((tabName != null && tabName.toLowerCase().equals(table))) {
												fldColumn.setAlias(null); 
												SQLAgregFunc fn = new SQLAgregFunc("MAX");
												fn.addParam(fldColumn);
												subquery.getSelect().addField(fn, colName);
												set.add(tabName + "." + colName);
											}											
										}
										if (expr2 instanceof UniQLColumn) {
											UniQLColumn fldColumn = (UniQLColumn)c.getLeftFragment(); 
											String tabName = fldColumn.getTableName();
											String colName = fldColumn.getName();
											if (!set.contains(tabName + "." + colName))
											if ((tabName != null && tabName.toLowerCase().equals(table))) {
												fldColumn.setAlias(null); 
												SQLAgregFunc fn = new SQLAgregFunc("MAX");
												fn.addParam(fldColumn);
												subquery.getSelect().addField(fn, colName);
												set.add(tabName + "." + colName);
											}																						
										}										
									}
								}
							}
							for (Iterator<String> iterator = joinedSet.iterator(); iterator.hasNext();) {
								String tableName = iterator.next();
								query.addField(tableName + ".*");
							}
							String xml = subquery.transToXML();
							xml = StringUtils.replace(xml, "\""+table+"\"", "\""+repl+"\"");
							try {
								Document doc = DocumentHelper.parseText(xml);
								Element node = doc.getRootElement();
								subquery = (UniQLSubQuery)subquery.xmlToExpr(node);
							} catch (Exception e) {
								System.out.println(e.getMessage());
							} 
							subquery.as(table);							
							remove = true;
						}						
					}						
				} else {
					
				}
			} else if (obj instanceof SQLFunc) {
				SQLFunc column = (SQLFunc)obj;
				table = fromName;
				if (table.equals(fromName)) {
					remove = generateGroupByQueryByColumn(query, subquery, expr, column, table, whereAliasMap);
				}				
			}
		}
		if (groupBy.getExprList() != null && groupBy.getExprList().size() == 1) {
			Object obj = groupBy.getExprList().get(0); 
			if (obj instanceof UniQLValue) remove = true; 
		}
		if (remove) query.removeGroupBy();
		return query;
	}
		
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#isGroupByRewrite()
	 */
	public boolean isGroupByRewrite() {
		return true;
	}
	
	// ----- LOCK & UNLOCK TABLE ----------------------------------------------
	
	public String showProcessList() {
		return "SELECT s.session_id AS Id, s.* FROM sys.dm_exec_sessions AS s";
	}
}
